package com.tejava.DB;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;




public class DbHelper extends SQLiteOpenHelper {

	static final String dbName="historyDBWithDate";
	static final String historyTable="History";
	public static final String colID="StoreID";
	public static final String colName="StoreName";
	public static final String colDate="StoreDate";
	public static final String colLocation="StoreLocation";
	public static final String colPhone="StorePhone";
	public static final String colMapX="StoreMapX";
	public static final String colMapY="StoreMapY";
	
	
	static final String viewEmps="ViewEmps";
	
	
	public DbHelper(Context context) {
		super(context, dbName, null,33);
		
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
		
		db.execSQL("CREATE TABLE "+historyTable+" ("+colID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
				colName+" TEXT, "+colDate+" TEXT,"+colLocation+" TEXT, "+colPhone+" TEXT, "+ colMapX + " TEXT, "+colMapY+" TEXT );");
		
		
		//Inserts pre-defined departments
		InsertDepts(db);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
		db.execSQL("DROP TABLE IF EXISTS "+historyTable);
		
		
//		db.execSQL("DROP TRIGGER IF EXISTS dept_id_trigger");
//		db.execSQL("DROP TRIGGER IF EXISTS dept_id_trigger22");
//		db.execSQL("DROP TRIGGER IF EXISTS fk_empdept_deptid");
//		db.execSQL("DROP VIEW IF EXISTS "+viewEmps);
		onCreate(db);
	}
	
	public void RemoveAllHistory(){
		
		SQLiteDatabase db= this.getWritableDatabase();
		
		db.execSQL("DROP TABLE IF EXISTS "+historyTable);
		db.execSQL("CREATE TABLE "+historyTable+" ("+colID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
				colName+" TEXT, "+colDate+" TEXT,"+colLocation+" TEXT, "+colPhone+" TEXT, "+ colMapX + " TEXT, "+colMapY+" TEXT );");
		
		
	}
	
	 public void AddHistory(History emp)
	{
		 
		 
		 SQLiteDatabase db= this.getWritableDatabase();
		 
		Cursor cur= db.rawQuery("Select * from "+historyTable 
				+" where "+colName
				+" = '"+ emp.getName()+"';", null);
		
		if(cur.getCount() >= 1){
			db.execSQL("Delete from "+ historyTable
					+" where "+colName
					+" = '"+ emp.getName()+"';");
		}
		else{
			
		}
		
		ContentValues cv=new ContentValues();
		
		cv.put(colName, emp.getName());
		cv.put(colDate, emp.getDate());
		cv.put(colLocation, emp.getLocation());
		cv.put(colPhone, emp.getPhone());
		cv.put(colMapX, emp.getMapX());
		cv.put(colMapY, emp.getMapY());
		//cv.put(colDept,2);
		
		db.insert(historyTable, colName, cv);
		db.close();
		cur.close();
		
		
	}
	 
	 public int getHistoryCount()
	 {
		SQLiteDatabase db=this.getWritableDatabase();
		Cursor cur= db.rawQuery("Select * from "+historyTable, null);
		int x= cur.getCount();
		cur.close();
		return x;
	 }
	 
	 public Cursor getAllHistorys()
	 {
		 SQLiteDatabase db=this.getWritableDatabase();
		 
		 
		 
		 //Cursor cur= db.rawQuery("Select "+colID+" as _id , "+colName+", "+colAge+" from "+employeeTable, new String [] {});
		 Cursor cur= db.rawQuery("SELECT DISTINCT "+colID+", "
				 +colName+", "
				 +colDate+", "
				 +colLocation+", "
				 +colPhone+", "
				 +colMapX+", "
				 +colMapY+" "+" FROM "
				 +historyTable + " ORDER BY "
				 +colID +" DESC ",null);
		 return cur;
		 
	 }
	 
	 
	 
	 public void InsertDepts(SQLiteDatabase db)
	 {
		 ContentValues cv=new ContentValues();
//			cv.put(colDeptID, 1);
//			cv.put(colDeptName, "Sales");
//			db.insert(deptTable, colDeptID, cv);
//			cv.put(colDeptID, 2);
//			cv.put(colDeptName, "IT");
//			db.insert(deptTable, colDeptID, cv);
//			cv.put(colDeptID, 3);
//			cv.put(colDeptName, "HR");
//			db.insert(deptTable, colDeptID, cv);
//			db.insert(deptTable, colDeptID, cv);
			
	 }
	 
	 
	 public int UpdateHsty(History emp)
	 {
		 SQLiteDatabase db=this.getWritableDatabase();
		 ContentValues cv=new ContentValues();
		 cv.put(colName, emp.getName());
		 cv.put(colDate, emp.getDate());
		 cv.put(colLocation, emp.getLocation());
		 cv.put(colPhone, emp.getPhone());
		 cv.put(colMapX, emp.getMapX());
		 cv.put(colMapY, emp.getMapY());
		 return db.update(historyTable, cv, colID+"=?", new String []{String.valueOf(emp.getID())});
		 
	 }
	 
	 public void DeleteEmp(History emp)
	 {
		 SQLiteDatabase db=this.getWritableDatabase();
		 db.delete(historyTable,colID+"=?", new String [] {String.valueOf(emp.getID())});
		 db.close();
		 
		
		
	 }

}
